package virtualinit.util;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author HuangHuai on 2019-06-24 09:33
 */

public class JdbcUtil {
    static Log log = Log.instance;
    static String defaultUrl = "jdbc:mysql://localhost:3306";

    public static Connection getConnection(String url, String user, String password) {
        if (url == null) {
            url = defaultUrl;
        }
        //        if (!url.contains("connectTimeout")) { //&connectTimeout=60000
        //            if (url.contains("&")) {
        //                url += "&connectTimeout=6000&autoReconnect=true";
        //            }else {
        //                url += "?connectTimeout=6000&autoReconnect=true";
        //            }
        //        }
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            throw new RuntimeException("获取数据库连接失败,url=" + url, e);
        }
    }

    public static long count(Connection conn, String sql) {
        return (long) queryOnlyOneCell(conn, sql);
    }

    public static Object queryOnlyOneCell(Connection conn, String sql, Object... args) {
        Object val = null;
        try {
            PreparedStatement prep = getPreparedStatement(conn, sql, args);
            ResultSet rs = prep.executeQuery();
            for (int i = 0; i < 2; i++) {
                if (rs.next()) {
                    if (i > 0) {
                        log.error("希望获得一个结果，但是查询到超过一个. sql={}", CommonUtil.format(sql, args));
                        break;
                    }
                    val = rs.getObject(1);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("执行sql出错sql=" + CommonUtil.format(sql, args), e);
        }
        return val;
    }

    public static List<Map<String, Object>> executeToMap(Connection conn, String sql) {
        PreparedStatement prep = null;
        try {
            prep = conn.prepareStatement(sql);
            return wrapRsToConsumer(prep.executeQuery(), JdbcUtil::rsToMap);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeQuietly(false, prep);
        }
    }

    public static void closeQuietly(boolean b, AutoCloseable... closeables) {
        if (closeables != null && closeables.length > 0) {
            for (AutoCloseable closeable : closeables) {
                if (closeable != null) {
                    try {
                        closeable.close();
                    } catch (Exception e) {
                        // ignore
                    }
                }
            }
        }
    }

    public static void closeQuietly(AutoCloseable... closeables) {
        if (closeables != null && closeables.length > 0) {
            for (AutoCloseable closeable : closeables) {
                if (closeable != null) {
                    try {
                        closeable.close();
                    } catch (Exception e) {
                        // ignore
                    }
                }
            }
        }
    }

    public static <T> List<T> executeToBean(Class<T> type, Connection conn, String sql, Object... args) {
        if (type == null)
            type = (Class<T>) HashMap.class;
        try {
            PreparedStatement prep = getPreparedStatement(conn, sql, args);
            List<T> result = wrapRsToConsumerBeanType(prep.executeQuery(), type, JdbcUtil::rsToBean);
            closeQuietly(false, prep);
            return result;
        } catch (Exception e) {
            log.error("将ResultSet转换为Map错误：" + e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }


    public static List<Map<String, Object>> executeToMap(Connection conn, String sql, Object... args) {
        try {
            PreparedStatement prep = getPreparedStatement(conn, sql, args);
            List<Map<String, Object>> maps = wrapRsToConsumer(prep.executeQuery(), JdbcUtil::rsToMap);
            closeQuietly(false, prep);
            return maps;
        } catch (Exception e) {
            throw new RuntimeException("将ResultSet转换为Map错误：" + e.getMessage(), e);
        }
    }

    public static Map<String, Object> executeToMapSingleRow(Connection conn, String sql, Object... args) {
        List<Map<String, Object>> data = executeToMap(conn, sql, args);
        if (data != null && !data.isEmpty())
            return data.get(0);
        return new HashMap<>(1);
    }


    public static PreparedStatement setParam(PreparedStatement prep, Object... args) throws SQLException {
        for (int i = 0; i < args.length; i++) {
            if (args[i] == null) {
                prep.setNull(i + 1, Types.VARCHAR);
            } else {
                prep.setObject(i + 1, args[i]);
            }
        }
        return prep;
    }

    public static PreparedStatement getPreparedStatement(Connection conn, String sql, Object... args) throws SQLException {
        PreparedStatement prep = conn.prepareStatement(sql);
        if (args != null && args.length != 0) {
            for (int i = 0; i < args.length; i++) {
                if (args[i] == null) {
                    prep.setNull(i + 1, Types.VARCHAR);
                } else {
                    prep.setObject(i + 1, args[i]);
                }
            }
        }
        return prep;
    }


    public static List<Map<String, Object>> rsToMap(ResultSet rs) {
        List<Map<String, Object>> list = new ArrayList<>(1024);
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                HashMap<String, Object> map = new HashMap<>(columnCount * 2);
                for (int j = 1; j <= columnCount; j++) {
                    map.put(metaData.getColumnLabel(j), rs.getObject(j));
                }
                list.add(map);
            }
        } catch (Exception e) {
            log.error("从ResultSet获取数据失败：" + e.getMessage(), e);
        }
        return list;
    }

    public static <T> List<T> rsToBean(ResultSet rs, Class<T> type) {
        if (type == null)
            throw new RuntimeException("把resultset转换为bean错误，bean type不能为空");

        List<T> data = new ArrayList<>();
        try {
            BeanInfo beanInfo = Introspector.getBeanInfo(type, Object.class);
            PropertyDescriptor[] list = beanInfo.getPropertyDescriptors();
            Map<String, PropertyDescriptor> nameToPropdes = Arrays.stream(list).collect(Collectors.toMap(e -> e.getName(), e -> e));
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                T o = type.newInstance();
                for (int j = 1; j <= columnCount; j++) {
                    PropertyDescriptor propertyDescriptor = nameToPropdes.get(metaData.getColumnLabel(j));
                    if (propertyDescriptor != null) {
                        propertyDescriptor.getWriteMethod().invoke(o, rs.getObject(j));
                    }
                }
                data.add(o);
            }
        } catch (Exception e) {
            throw new RuntimeException("从ResultSet获取数据失败：" + e.getMessage(), e);
        }
        return data;
    }


    public static <Out> Out wrapRsToConsumer(ResultSet rs, Fun.F11Throw<ResultSet, Out> consumer) {
        try {
            return consumer.apply(rs);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static <T> List<T> wrapRsToConsumerBeanType(ResultSet rs, Class<T> type, Fun.F21Throw<ResultSet, Class<T>, List<T>> consumer) {
        try {
            return consumer.apply(rs, type);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return null;
    }

    public static <Out> Out executeToConsumer(Connection conn, String sql, Fun.F11Throw<ResultSet, Out> consumer) {
        PreparedStatement prep = null;
        try {
            prep = conn.prepareStatement(sql);
            ResultSet rs = prep.executeQuery();
            return consumer.apply(rs);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            closeQuietly(prep);
        }
        return null;
    }

    public static int update(Connection conn, String sql, Object... args) {
        PreparedStatement prep = null;
        try {
            prep = getPreparedStatement(conn, sql, args);
            return prep.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(CommonUtil.format("执行sql出错,Msg：{}. sql={}", e.getMessage(), sql), e);
        } finally {
            closeQuietly(prep);
        }
    }

    public static PreparedStatement updateBatch(PreparedStatement prep, Object... args) {
        try {
            for (int i = 0; i < args.length; i++) {
                if (args[i] == null) {
                    prep.setNull(i + 1, Types.VARCHAR);
                } else {
                    prep.setObject(i + 1, args[i]);
                }
            }
            prep.addBatch();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return prep;
    }

    /**
     * 执行sql,返回一行行数据,每行数据使用Map封装。使用consumer消费每行数据(不是一次把所有数据都加载到java内存而是使用游标cursor)
     */
    public static void exeCursorMap(Connection conn, String sql, Fun.F10Throw<Map<String, Object>> consumer) {
        PreparedStatement prep = null;
        try {
            prep = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            prep.setFetchSize(Integer.MIN_VALUE);
            ResultSet rs = prep.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                HashMap<String, Object> map = new HashMap<>(columnCount * 2);
                for (int j = 1; j <= columnCount; j++) {
                    map.put(metaData.getColumnLabel(j), rs.getObject(j));
                }
                consumer.apply(map);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeQuietly(prep);
        }
    }


    public static String quoteIdentifier(String identifier) {
        if (identifier.contains("`"))
            return identifier;
        else
            return '`' + identifier + '`';

    }

    //jdbc:mysql://localhost:3306/db
    public static String getHostPortFromUrl(String url) {
        // // idx
        int i = url.indexOf("//");
        // : idx
        int i2 = url.indexOf(":", i);
        //  / db idx
        int i3 = url.indexOf("/",i2);
        //  ? idx
        int i4 = url.indexOf("?",i2);

        int portIdx = -1;
        if (i3 != -1) {
            portIdx = i3;
        }else if(i4!=-1) {
            portIdx = i4;
        }else {
            portIdx = url.length();
        }
        return url.substring(i + 2, portIdx);
    }
}
